package cn.cvs.repository;

import cn.cvs.pojo.SysUser;
import cn.cvs.vo.UserInfo;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

//自定义复杂查询,如条件分页查询
public interface UserRepository extends JpaRepository<SysUser, Long>, JpaSpecificationExecutor<SysUser> {
    //自定义条件查询
    List<SysUser> findByUsrNameAndUsrPassword(String usrName  , String usrPassword);
    Long countByUsrName(String usrName);
    List<SysUser>  findByUsrNameLike(String usrName);

    //复杂查询 HQL ->用对象+属性  --> 代替  表 +列 来查询  ,?1  ?2 ?3 为参数占位符
    @Query("select u from SysUser u where u.usrRoleId=?1")
    List<SysUser> findByRoleId(Long roleId);

    //原生sql @Param为命名参数注解
    @Query(value = "select u.* from sys_user u where u.usr_id=:usrId",nativeQuery = true)
    SysUser findByUsrId(@Param("usrId") Long usrId);

    //修改删除及事务
    @Transactional(timeout = 10)
    @Modifying
    @Query("update SysUser u set u.usrName=?1 where u.usrId=?2")
    public int modifyNameById(String usrName,Long usrId);

    //SysUser 实体中的命名查询
    //如果希望根据接口方法名来实现数据库操作,这应命名为 fundUsersByUsrName
    List<SysUser> findSysUsersByName(String usrName);

    @Query("select u from SysUser u where u.usrRoleId=?1")
    Page<SysUser> findPageByUsrRoleId(Long roleId,Pageable pageable);

    //多表关联查询
    @Query("select u.usrId as usrId,u.usrName as usrName,u.usrPassword as usrPassword" +
            ",u.usrRoleId as usrRoleId ,u.usrFlag as usrFlag,r.roleName as roleName " +
            " from SysUser u ,SysRole r where u.usrRoleId =r.roleId and u.usrId=?1")
   //注意查询要写别名,否则vo无法封装属性的值
    /* @Query("select u.usrName as usrName,r.roleName" +
            " from SysUser u ,SysRole r where u.usrRoleId =r.roleId and u.usrId=?1")*/
    UserInfo getUserInfo(Long usrId);
}
